--/
/*
        先查询 被查询物料的一组数据，然后将数据存储到一张临时表，然后从最顶阶
        开始添加阶次数据
*/
CREATE PROCEDURE sp_queryUseTab
        @bomTopCode varchar(50),--母件编号
        @bomVersion varchar(20),--母件版本
        @materielCode varchar(50),--查询物料
        @type varchar(20) --用途表类型
as
          --临时表存储查询的数据
        create table #bt_bom_useTab_rank(
                rank varchar(50),
                bomCode varchar(50),
                version varchar(20),             
                materielCode varchar(50),
                materielName varchar(50),
                materielTypeName varchar(100), 
	        materielStandard varchar(100),
	        pCode varchar(50),
                pVersion varchar(20),
                quantityPer varchar(20),
                effectiveDate varchar(20),
                expiryDate varchar(20),
                unitNumTypeName varchar(100),--用量类型
                fixedLoss varchar(20),--固定损耗量
                changeLossRate varchar(20),--变动损耗率
                manuLossTypeName varchar(100),--损耗类型
	        issuedWayName varchar(100),--发料方式
	        issuedProcessName varchar(100),--发料工序
	        issuedWareName varchar(100),--发料仓库
	        issuedGoodsName varchar(100),--发料货位
	        auditStatus varchar(10),--审核状态
	        ersatz varchar(150),--代用物料
                orders int
        )
         --临时表存储bom树查询数据
        create table #bt_bom_bomTree_view(
                bomlevel int,
                code varchar(50),
                bomCode nvarchar(50),
                version nvarchar(50),
                pCode nvarchar(50),
                bomPCode nvarchar(50),
                pVersion nvarchar(50),
                materielCode nvarchar(50),
                materielName nvarchar(50),
                effectiveDate varchar(20),
                expiryDate varchar(20),
                quantityPer varchar(20),
                fixedLoss varchar(20),
                changeLossRate varchar(20),
                unitNumTypeCode varchar(50),
                unitNumTypeName varchar(100),
	        materielTypeName varchar(100), 
	        materielStandard varchar(100),
	        manuLossTypeName varchar(100),
	        issuedWayName varchar(100),
	        issuedProcessName varchar(100),
	        issuedWareName varchar(100),
	        issuedGoodsName varchar(100),
	        auditStatus varchar(10),--审核状态
                flag int 
        )
        --查询bomtree数据
       insert into #bt_bom_bomTree_view exec sp_bomDrillDown3 @bomTopCode, @bomVersion
           --查询在该bom中有多少被查询物料
        --建游标循环物料          
        declare @orders int --最终结果排序号
        declare @code varchar(50)--查询物料的所属bom编号
        declare @version varchar (20)--查询物料的所在bom版本
        declare @pCode varchar(50)--查询物料的父级编号+版本
        declare @bomPCode varchar(50)--查询物料的父级编号
        declare @pVersion varchar(20)--查询物料的父级版本
        declare @mcount varchar(20)--查询物料所在bom的用量
        declare @mfixedLoss varchar(20)--查询物料所在bom的固定损耗量
        declare @mchangeLossRate varchar(20)--查询物料所在bom的损耗率
        declare @mtype varchar(50)--查询物料所在bom的用量类型
        select @orders = 0
        --在bom树中查询待查询物料
        declare c_scroll cursor for select code,version,pCode,bomPCode,pVersion,quantityPer,fixedLoss,changeLossRate,unitNumTypeCode from #bt_bom_bomTree_view where materielCode = @materielCode and pCode != '0'
        open c_scroll
        fetch next from c_scroll into @code,@version,@pCode,@bomPCode,@pVersion,@mcount,@mfixedLoss,@mchangeLossRate,@mtype
        while(@@fetch_status = 0)
        begin
                print 'code----' +@code 
                declare @fixedLoss varchar(50)--固定损耗量
                declare @unitNumTypeCode varchar(50)--用量类型
                declare @changeLossRate varchar(20)--变动损耗率
                declare @tversion varchar(20)--查询的bom的版本
                declare @tbomlevel varchar(20)--查询的bom的层级
                declare @bcode varchar(50)     --查询bom的编号
                declare @count varchar(20)    --使用量
                declare @bpCode varchar(50) --父级code + 父级版本
                  -- select * from  #bt_bom_bomTree_view
               --  select code,bomCode,version,pCode,pVersion,bomlevel from #bt_bom_bomTree_view where code = @code and version = @version                      
               declare @num varchar(20) 
                  /*
                        在用量计算的时候使用用量字段，根据用量类型的状态，
                        如果选择为固定用量那么就是在生产母件的时候不管产量为多少子件的使用量都是固定的，
                        如果为变动用量那么现在将输入的用量字段作为单位用量使用，然后计算使用量
                        注：在用量的计算中要使用损耗率(固定损耗量不受使用总量的影响,固定)，损耗量(损耗率根据使用总量乘以损耗量，非固定)
               */
               --计算查询物料的用量
                  /*  if(@unitNumTypeCode = '')--用量类型为固定用量
                        begin*/  
                                   /*  end*/
                       /* else
                        begin
                                
                                if(@mfixedLoss != '' and @mfixedLoss != null)
                                begin
                                        select @num = cast()
                                end
                                else if(@mchangeLossRate != '' and @mchangeLossRate != null)
                                begin
                                
                                end
                        end*/                     
                  declare @number decimal(20,2)--查询物料用量
                  select @number = cast(@mcount as decimal(20,2))    
                  if(@mfixedLoss = '' or @mfixedLoss = null)--固定损耗量
                  begin
                          print '@mchangeLossRate====' + @mchangeLossRate
                          select @number = cast((@number + cast(@mcount as decimal(20,2)) * cast(@mchangeLossRate as int)*0.01) as decimal(20,2))
                  end
                  else if(@mchangeLossRate = '' or @mchangeLossRate = null )--损耗率
                  begin                                      
                          print '@mfixedLoss====' + @mfixedLoss
                          select @number =@number + cast(@mfixedLoss as decimal(20,2))                                      
                  end
                 declare @ersatzStr varchar(150) --代用物料按钮
                 declare @ersatzNum int --代用料数量
                 select @ersatzStr = ''
               select @num = @number
               declare @isErsatz varchar(2)--是否可代用
               select @isErsatz = ''
               declare @level int --查询bom阶次
               if(@type='single')--单阶用途表
               begin
                       print @num + '===num'
                       --判断bom是否为母件
                       select @level=bomlevel  from #bt_bom_bomTree_view where code+'_' + version = @pCode 
                       if(@level != 1)
                       begin
                                 --判断当前bom件是否能被代用
                                select @isErsatz=isReplace  from b_bom_info2 where status = '1' and bomCode = @bomPCode and version = @pVersion
                                --查询当前bom是否有代用物料
                                select @ersatzNum=count(ersatzMaterielCode) from b_ersatz_info where status = '1' and BOMCode +'_'+ BOMVersion = @pCode 
                                 if(@ersatzNum > 0)
                                 begin
                                        select @ersatzStr = '<button class="form-control" style="width:60px;"  onclick="getErsatz(''' +@bomPCode+''','''+@pVersion+''','''+@num+''');">代用...</button>'
                                end
                                else
                                begin
                                        select @ersatzStr = ''
                                end     
                       end
                       else
                       begin
                                select @ersatzStr = ''
                       end            
                      insert into #bt_bom_useTab_rank  select 
                       bomlevel,code,version,materielCode,materielName,materielTypeName,materielStandard,case pCode when '0' then '' else pCode end as 'pCode',pVersion,@num,effectiveDate,expiryDate,unitNumTypeName,changeLossRate,fixedLoss,
	               manuLossTypeName,issuedWayName,issuedProcessName,issuedWareName,issuedGoodsName,auditStatus,@ersatzStr,@orders from #bt_bom_bomTree_view where code+'_' + version = @pCode                       
               end
               else if (@type = 'multi')--多阶用途表
               begin
                        print '多阶'
                        --循环查询bom从子级到父级
                       declare c_mScroll cursor for  with temp(code,version,pCode,quantityPer,fixedLoss,changeLossRate,unitNumTypeCode) 
                         as(    select code,version,pCode,quantityPer,fixedLoss,changeLossRate,unitNumTypeCode from #bt_bom_bomTree_view where code+ '_' + version = @pCode 
                         union all
                        select b.code, b.version,b.pCode,b.quantityPer,b.fixedLoss,b.changeLossRate,b.unitNumTypeCode from #bt_bom_bomTree_view b inner join temp b1 
                        on b.code + '_' + b.version = b1.pCode ) select * from temp  
                        open c_mScroll
                        fetch next from c_mScroll into @bcode,@tversion,@bpCode,@count, @fixedLoss,@changeLossRate,@unitNumTypeCode
                        while(@@fetch_status = 0)
                        begin                            
                                print 'num----' + @num
                                select @orders = @orders +1
                                  --select @num = cast(cast(@num as decimal(20,2)) * cast(@count as decimal(20,2)) as decimal(20,2))
                               /* if(@unitNumTypeCode = '')--用量类型为固定用量(当母件制造时有制造需求量时用量类型开始使用)
                                begin
                                        
                                end
                                else --变动用量
                                begin
                                        
                                end*/
                               --  declare @number decimal(20,2)                          right(REPLICATE('.',(bomlevel*3))+CAST(bomlevel AS varchar(50)),((bomlevel+1)*3)) as 'bomlevel'      
                               --判断bom是否为母件
                               select @level=bomlevel  from #bt_bom_bomTree_view where code = @bcode and version = @tversion 
                                if(@level != 1)
                                begin
                                         --判断当前bom件是否能被代用
                                        select @isErsatz=isReplace  from b_bom_info2 where status = '1' and bomCode = @bcode and version = @tversion
                                        --查询当前bom是否有代用物料
                                        select @ersatzNum=count(ersatzMaterielCode) from b_ersatz_info where status = '1' and BOMCode =@bcode and  BOMVersion = @tversion 
                                        if(@ersatzNum > 0)
                                        begin
                                                select @ersatzStr = '<button class="form-control" style="width:60px;" onclick="getErsatz(''' +@bcode+''','''+@tversion+''','''+@num+''');">代用...</button>'
                                        end
                                        else
                                        begin
                                                select @ersatzStr = ''
                                        end      
                                end
                                else
                                begin
                                        select @ersatzStr = ''
                                end     
                              insert into  #bt_bom_useTab_rank  select 
                               right(REPLICATE('.',((bomlevel-1)*3))+CAST(bomlevel AS varchar(50)),(bomlevel*3)) as 'bomlevel' ,code,version,materielCode,materielName,materielTypeName,materielStandard,case pCode when '0' then '' else pCode end as 'pCode',pVersion,@num,effectiveDate,expiryDate,unitNumTypeName,changeLossRate,fixedLoss,
	                       manuLossTypeName,issuedWayName,issuedProcessName,issuedWareName,issuedGoodsName,auditStatus,@ersatzStr,@orders
                               from #bt_bom_bomTree_view where code = @bcode and version = @tversion       
                              print '多阶用途表本阶用量====' + @num + '====父级====' +@bpCode + '====用量===' +@count
                               if(@bpcode != '0')                           
                               begin
                                        if(@fixedLoss = '' or @fixedLoss = null)--固定损耗量
                                        begin
                                                print '@changeLossRate====' + @changeLossRate
                                                select @num = cast( ( ( cast(@num as decimal(20,2)) * cast(@count as decimal(20,2)) )* cast(@changeLossRate as int)*0.01 ) as decimal(20,2) ) + cast( cast(@num as decimal(20,2)) * cast(@count as decimal(20,2)) as decimal(20,2))
                                        end
                                        else if(@changeLossRate = '' or @changeLossRate = null )--损耗率
                                        begin                                      
                                                 print '@fixedLoss====' + @fixedLoss
                                                 select @num = cast( cast(@num as decimal(20,2)) * ( cast(@fixedLoss as decimal(20,2)) + cast(@count as decimal(20,2)) )  as decimal(20,2) )                                   
                                        end
                                        else
                                        begin
                                                select @num = @num
                                        end 
                               end                                                                                           
                                fetch next from c_mScroll into  @bcode,@tversion,@bpCode,@count, @fixedLoss,@changeLossRate,@unitNumTypeCode
                        end
                        close c_mScroll
                        deallocate c_mScroll  
               end
               else
               begin
                         print '综合/最高阶'
                         --循环查询bom从子级到父级  
                       declare c_mScroll cursor for  with temp(code,version,pCode,quantityPer,fixedLoss,changeLossRate,unitNumTypeCode) 
                         as(    select code,version,pCode,quantityPer,fixedLoss,changeLossRate,unitNumTypeCode from #bt_bom_bomTree_view where code+ '_' + version = @pCode 
                         union all
                        select b.code, b.version,b.pCode,b.quantityPer,b.fixedLoss,b.changeLossRate,b.unitNumTypeCode from #bt_bom_bomTree_view b inner join temp b1 
                        on b.code + '_' + b.version = b1.pCode ) select * from temp  
                        open c_mScroll
                        fetch next from c_mScroll into @bcode,@tversion,@bpCode,@count, @fixedLoss,@changeLossRate,@unitNumTypeCode
                        while(@@fetch_status = 0)
                        begin                            
                                print 'num----' + @num
                                select @orders = @orders +1
                                 --select @num = cast(cast(@num as decimal(20,2)) * cast(@count as decimal(20,2)) as decimal(20,2))
                               /* if(@unitNumTypeCode = '')--用量类型为固定用量(当母件制造时有制造需求量时用量类型开始使用)
                                begin
                                        
                                end
                                else --变动用量
                                begin
                                        
                                end*/
                               --  declare @number decimal(20,2)                
                               insert into  #bt_bom_useTab_rank  select 
                               bomlevel,code,version,materielCode,materielName,materielTypeName,materielStandard,pCode,pVersion,@num,effectiveDate,expiryDate,changeLossRate,fixedLoss,
	                       manuLossTypeName,issuedWayName,issuedProcessName,issuedWareName,issuedGoodsName,@orders from #bt_bom_bomTree_view where code = @bcode and version = @tversion        
                               if(@bpcode != '0')                           
                               begin
                                        if(@fixedLoss = '' or @fixedLoss = null)--固定损耗量
                                        begin
                                                print '@changeLossRate====' + @changeLossRate
                                                select @num = cast((cast(@num as decimal(20,2))+ cast(@count as decimal(20,2)) * cast(@changeLossRate as int)*0.01) as decimal(20,2))
                                        end
                                        else if(@changeLossRate = '' or @changeLossRate = null )--损耗率
                                        begin                                      
                                                 print '@fixedLoss====' + @fixedLoss
                                                 select @num =cast(@num as decimal(20,2)) + cast(@fixedLoss as decimal(20,2))                                      
                                        end
                                        else
                                        begin
                                                select @num = @num
                                        end 
                               end                                                                                           
                                fetch next from c_mScroll into  @bcode,@tversion,@bpCode,@count, @fixedLoss,@changeLossRate,@unitNumTypeCode
                        end
                        close c_mScroll
                        deallocate c_mScroll  
                     end                                                
               fetch next from   c_scroll into @code,@version,@pCode,@bomPCode,@pVersion,@mcount,@mfixedLoss,@mchangeLossRate,@mtype
        end
        close c_scroll
        deallocate c_scroll
        select * from  #bt_bom_useTab_rank order by orders desc
/
